package com.jxc.jxc01.grc.dao.impl;


import com.jxc.jxc01.grc.dao.IVendorDao;
import com.jxc.jxc01.grc.entity.JDBCConn;
import com.jxc.jxc01.grc.entity.Vendor;
import sun.security.rsa.RSASignature;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class VendorDaoImpl implements IVendorDao {
    /**
     * 添加供应商
     * @param vendor
     * @return
     */
    @Override
    public int addVendor(Vendor vendor) {
        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            grc = DriverManager.getConnection("jdbc:mysql://172.30.2.4:3306/jxc",
//                    "grc",
//                    "123456");

            String sql = "INSERT INTO supplier(suppliername,headname,phone,address,bank,Creationtime,updatetime) " +
                         "VALUES (?,?,?,?,?,?,?) ";
            PreparedStatement pstmt=JDBCConn.connect(sql);
            pstmt.setString(1,vendor.getSuppliername());
            pstmt.setString(2,vendor.getHeadname());
            pstmt.setString(3,vendor.getPhone());
            pstmt.setString(4,vendor.getAddress());
            pstmt.setString(5,vendor.getBank());
            pstmt.setString(6,vendor.getCreationtime());
            pstmt.setString(7,vendor.getUpdatetime());
            int i = pstmt.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据id删除供应商
     * @param id
     * @return
     */
    @Override
    public int deleteVendor(int id) {
        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            grc = DriverManager.getConnection("jdbc:mysql://172.30.2.4:3306/jxc",
//                    "grc",
//                    "123456");
            String sql = "DELETE FROM supplier WHERE id = ?";
            PreparedStatement pstmt=JDBCConn.connect(sql);
            pstmt.setInt(1,id);
            int i = pstmt.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 修改供应商信息
     * @param vendor
     * @return
     */
    @Override
    public int reviseVendor(Vendor vendor) {
        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            grc = DriverManager.getConnection("jdbc:mysql://172.30.2.4:3306/jxc",
//                    "grc", "123456");
//            更新数据
            String sql = "UPDATE supplier SET suppliername=?,headname=?,phone=?,address=?,bank=?,updatetime=? WHERE id=?";
            PreparedStatement pstmt=JDBCConn.connect(sql);
            pstmt.setString(1,vendor.getSuppliername());
            pstmt.setString(2,vendor.getHeadname());
            pstmt.setString(3,vendor.getPhone());
            pstmt.setString(4,vendor.getAddress());
            pstmt.setString(5,vendor.getBank());
            pstmt.setString(6,vendor.getUpdatetime());
            pstmt.setInt(7, vendor.getId());
            int i = pstmt.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return 0;
    }

    @Override
    public List<Vendor> queryVendors() {
        List<Vendor> list = new ArrayList<>();
        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            grc = DriverManager.getConnection("jdbc:mysql://172.30.2.4:3306/jxc",
//                    "grc", "123456");
            String sql = "SELECT * FROM supplier";
            PreparedStatement pstmt=JDBCConn.connect(sql);
            ResultSet re = pstmt.executeQuery();
            while (re.next()) {
                Vendor v = new Vendor();
                v.setId(re.getInt("id"));
                v.setSuppliername(re.getString("suppliername"));
                v.setHeadname(re.getString("headname"));
                v.setPhone(re.getString("phone"));
                v.setAddress(re.getString("address"));
                v.setBank(re.getString("bank"));
                v.setCreationtime(re.getString("Creationtime"));
                v.setUpdatetime(re.getString("updatetime"));
                list.add(v);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public int queryVendorsTotalCount() {
        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            grc = DriverManager.getConnection("jdbc:mysql://172.30.2.4:3306/jxc",
//                    "grc", "123456");
            String sql = "SELECT count(*) FROM supplier";
            PreparedStatement pstmt=JDBCConn.connect(sql);
            ResultSet re = pstmt.executeQuery();
            while (re.next()) {
                int anInt = re.getInt(1);
                return anInt;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;

    }

    @Override
    public List<Vendor> queryVendorsByFuzzyStr(String suppliername) {
        List<Vendor> list = new ArrayList<>();
        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            grc = DriverManager.getConnection("jdbc:mysql://172.30.2.4:3306/jxc", "grc", "123456");
            String sql = "SELECT * FROM supplier WHERE suppliername like ?";

            PreparedStatement pstmt=JDBCConn.connect(sql);
            String a = "%"+suppliername + "%";
            pstmt.setString(1,a);
            ResultSet re = pstmt.executeQuery();
            while (re.next()) {
                Vendor v = new Vendor();
                v.setId(re.getInt("id"));
                v.setSuppliername(re.getString("suppliername"));
                v.setHeadname(re.getString("headname"));
                v.setPhone(re.getString("phone"));
                v.setAddress(re.getString("address"));
                v.setBank(re.getString("bank"));
                v.setCreationtime(re.getString("Creationtime"));
                list.add(v);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
}
